﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient

<System.ComponentModel.DataObjectAttribute(True)> _
Public Class BLL_ManageEnlistment_QueryWorkTrainingRecord

    Public Sub New()

    End Sub

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_Select(ByVal VOrganizationID As Integer, ByVal VolunteerId As String, ByVal VolunteerName As String, ByVal TEL As String, ByVal CheckStatus As String, ByVal UOrganizationID As String, ByVal annual As String, ByVal itemType As String) As DataTable

        Dim dat As New clsData

        Dim sql As New MyOrganizationDataFilter

        sql.AppendFormat("SELECT ")
        sql.AppendFormat("B.UOrganizationID, ")
        sql.AppendFormat("A.VolunteerId,F.VolunteerName,C.Text as GroupTypeText,ItemTypeName=cm.Text,GroupName = B.EnlistName, AllHour = etc.TrainingHour, B.StartDate ")
        sql.AppendFormat(",E.Text as CheckStatusText ")
        sql.AppendFormat("FROM EnlistTrainingRecord A ")
        sql.AppendFormat("inner join EnlistTrainingCourse etc on A.CourseID=etc.CourseID ")
        sql.AppendFormat("inner join EnlistHeader B on etc.EnlistID=B.EnlistID ")
        sql.AppendFormat("LEFT JOIN CodeMapping C on C.Value = 'W' and C.TableName='TrainingCourseGroup' and C.ColumnName='GroupType' ")
        sql.AppendFormat("LEFT JOIN CodeMapping E on A.Status=E.Value and E.TableName='TrainingRegister' and E.ColumnName='CheckStatus' ")
        sql.AppendFormat("LEFT JOIN CodeMapping cm on etc.CourseType=cm.Value and cm.TableName='CleanPlan' and cm.ColumnName='ItemType' ")

        sql.AppendFormat("INNER JOIN Volunteer F on A.VolunteerID = F.VolunteerID ")
        sql.AppendFormat("WHERE ")
        sql.AppendFormat("(F.VolunteerID IN (SELECT distinct VolunteerID FROM VOrganizationMapping WHERE VOrganizationID={0} ) or '{0}' =0 ) ", VOrganizationID)
        'sql.AppendFormat("AND (F.VillageID IN (SELECT RegionID FROM Region WHERE Parent='{0}' or '{0}' ='' )) ", Region)
        'sql.AppendFormat("AND (F.VillageID ='{0}' or '{0}' ='' ) ", Region2)
        sql.AppendFormat("AND (F.VolunteerId = '{0}' or '{0}'='') ", VolunteerId)
        sql.AppendFormat("AND (F.VolunteerName like '%{0}%' or '{0}'='') ", VolunteerName)
        sql.AppendFormat("AND (((F.MobilePhone like '%{0}%' or '{0}'='') or (F.OfficePhone like '%{0}%' or '{0}'=''))) ", TEL)
        sql.AppendFormat("AND (A.Status ='{0}' or '{0}' ='' ) ", CheckStatus)
        sql.AppendFormat("AND (B.UOrganizationID ='{0}' or '{0}' ='' ) ", UOrganizationID)

        'sql.AppendFormat("ORDER BY A.VolunteerID ")

        If annual <> "" Then
            sql.AppendFormat("and year(B.StartDate) = {0} ", annual)
        End If

        If itemType <> "" Then
            sql.AppendFormat("and etc.CourseType = {0} ", itemType)
        End If
        'MyObj.WriteLog(sql.ToString())
        Return dat.GetData(sql)

    End Function

End Class
